package com.system.enterprisemanagementsystem.mapper;

import com.system.enterprisemanagementsystem.pojo.Clazz;
import com.system.enterprisemanagementsystem.pojo.ClazzQueryParam;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface ClazzMapper {

    /**
     * 查询所有的员工及其对应的部门名称
     */
    List<Clazz> list(ClazzQueryParam clazzQueryParam);

    /**
     * 查询所有的班级信息
     * @return
     */
    @Select("select * from clazz")
    List<Clazz> selectAll();
    /**
     * 添加
     * @param clazz
     */
    void addClazz(Clazz clazz);

    /**
     * 根据id来查询班级信息
     * @param id
     * @return
     */
    @Select("select * from clazz where id = #{id}")
    Clazz getClazzById(int id);

    /**
     * 更新班级信息
     * @param clazz
     * @return
     */
    void updateClazz(Clazz clazz);

    /**
     * 根据id查询学生数量
     * @param clazzId
     */
    @Select("SELECT COUNT(*) FROM student WHERE clazz_id = #{clazzId}")
    Integer getStudentCountByClazzId(Integer clazzId);


    /**
     * 根据id删除班级信息
     * @param id
     * @return
     */
    @Delete("DELETE FROM clazz WHERE id = #{id}")
    int deleteClazzById(Integer id);


    @Select("""
        SELECT 
            c.name AS clazzName,
            COUNT(s.id) AS studentCount
        FROM clazz c
        LEFT JOIN student s ON c.id = s.clazz_id
        GROUP BY c.id, c.name
        ORDER BY c.id
    """)
    List<Map<String, Object>> getStudentCountData();
}
